import pymysql
from datetime import date

def init_db():
    conn = pymysql.connect(
        host='localhost', 
        user='root', 
        password='123',
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor
    )
    
    try:
        with conn.cursor() as cursor:
            # 创建数据库
            cursor.execute("CREATE DATABASE IF NOT EXISTS bank_system DEFAULT CHARACTER SET utf8mb4")
            cursor.execute("USE bank_system")
            
            # 创建管理员表
            cursor.execute("""
            CREATE TABLE IF NOT EXISTS adminInfo (
                adminID INT AUTO_INCREMENT PRIMARY KEY,
                username VARCHAR(20) NOT NULL UNIQUE,
                password VARCHAR(100) NOT NULL,
                fullname VARCHAR(50) NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )""")
            
            # 创建客户信息表
            cursor.execute("""
            CREATE TABLE IF NOT EXISTS userInfo (
                customerID INT AUTO_INCREMENT PRIMARY KEY,
                customerName VARCHAR(20) NOT NULL,
                PID VARCHAR(18) NOT NULL UNIQUE,
                telephone VARCHAR(11) NOT NULL,
                address VARCHAR(100),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )""")
            
            # 创建存款类型表
            cursor.execute("""
            CREATE TABLE IF NOT EXISTS depositType (
                savingID INT AUTO_INCREMENT PRIMARY KEY,
                savingName VARCHAR(50) NOT NULL,
                descript TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )""")
            
            # 创建银行卡表
            cursor.execute("""
            CREATE TABLE IF NOT EXISTS cardInfo (
                cardID VARCHAR(16) PRIMARY KEY,
                curID VARCHAR(3) DEFAULT 'RMB',
                openDate DATE DEFAULT (CURRENT_DATE),
                openMoney DECIMAL(15,2) NOT NULL CHECK (openMoney >= 1),
                balance DECIMAL(15,2) NOT NULL CHECK (balance >= 1),
                pass VARCHAR(6) DEFAULT '888888',
                IsReportLoss BOOLEAN DEFAULT FALSE,
                customerID INT NOT NULL,
                savingID INT NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (customerID) REFERENCES userInfo(customerID),
                FOREIGN KEY (savingID) REFERENCES depositType(savingID)
            )""")
            
            # 创建交易记录表
            cursor.execute("""
            CREATE TABLE IF NOT EXISTS tradeInfo (
                tradeID INT AUTO_INCREMENT PRIMARY KEY,
                tradeDate DATETIME DEFAULT CURRENT_TIMESTAMP,
                tradeType ENUM('存款', '取款', '转账支出', '转账收入', '开户') NOT NULL,
                tradeMoney DECIMAL(15,2) NOT NULL CHECK (tradeMoney > 0),
                cardID VARCHAR(16) NOT NULL,
                remark VARCHAR(255),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (cardID) REFERENCES cardInfo(cardID)
            )""")
            
            # 添加存款类型数据
            deposit_types = [
                ('活期存款', '无固定存期，可随时存取，存取金额不限'),
                ('定期存款', '选择存款期限，整笔存入，到期提取本息'),
                ('定活两便', '事先不约定存期，一次性存入，一次性支取'),
                ('零存整取', '每月固定存款，到期一次性支取本息')
            ]
            cursor.executemany("""
                INSERT IGNORE INTO depositType (savingName, descript)
                VALUES (%s, %s)
            """, deposit_types)
            
            # 添加管理员数据
            admins = [
                ('admin1', 'password1', '系统管理员'),
                ('admin2', 'password2', '高级管理员')
            ]
            cursor.executemany("""
                INSERT IGNORE INTO adminInfo (username, password, fullname)
                VALUES (%s, %s, %s)
            """, admins)
            
            # 添加客户数据
            customers = [
                ('张三', '110101199001011234', '13800138000', '北京市海淀区'),
                ('李四', '110101199002022345', '13900139000', '上海市浦东新区'),
                ('王五', '110101199003033456', '13700137000', '广州市天河区')
            ]
            cursor.executemany("""
                INSERT IGNORE INTO userInfo (customerName, PID, telephone, address)
                VALUES (%s, %s, %s, %s)
            """, customers)
            
            # 添加银行卡数据
            cards = [
                ('1010357612345678', 10000.00, 5000.00, '123456', 1, 1),
                ('1010357687654321', 20000.00, 15000.00, '654321', 2, 2),
                ('1010357698765432', 5000.00, 3000.00, '112233', 3, 1)
            ]
            cursor.executemany("""
                INSERT IGNORE INTO cardInfo (cardID, openMoney, balance, pass, customerID, savingID)
                VALUES (%s, %s, %s, %s, %s, %s)
            """, cards)
            
            # 添加交易记录数据
            transactions = [
                ('2023-10-01 09:30:25', '存款', 8500.00, '1010357612345678', '工资收入'),
                ('2023-10-01 10:15:18', '取款', 2000.00, '1010357687654321', 'ATM取款'),
                ('2023-10-02 14:20:33', '转账支出', 1500.00, '1010357612345678', '转账至李四'),
                ('2023-10-02 14:20:33', '转账收入', 1500.00, '1010357687654321', '来自张三转账'),
                ('2023-10-03 18:45:12', '取款', 1280.00, '1010357612345678', '商场消费'),
                ('2023-10-04 11:05:47', '存款', 5000.00, '1010357698765432', '现金存款')
            ]
            cursor.executemany("""
                INSERT IGNORE INTO tradeInfo (tradeDate, tradeType, tradeMoney, cardID, remark)
                VALUES (%s, %s, %s, %s, %s)
            """, transactions)
            
            conn.commit()
            print("数据库初始化成功！")
            
    except Exception as e:
        print(f"数据库初始化失败: {str(e)}")
        conn.rollback()
    finally:
        conn.close()

if __name__ == '__main__':
    init_db()